#!/bin/sh
ORACLE_BASE=/oracle/app/oracle
export ORACLE_BASE
ORACLE_SID=PROD
export ORACLE_SID
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_HOME

TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN

PATH=$ORACLE_HOME/bin:$PATH
export PATH
AWRLOG=/home/oracle/awrrpt/AWR_`date +%Y%m%d`.html
sqlplus /nolog << EOF  >/tmp/awr.out
conn / as sysdba
column l_snap_end new_value l_snap_end;
column l_snap_start new_value  l_snap_start
column l_dbid new_value l_dbid
column l_instance_number new_value l_instance_number
select dbid l_dbid from v\$database;
select instance_number l_instance_number from v\$instance;
select min(s.snap_id) l_snap_start ,max(s.snap_id) l_snap_end
from dba_hist_snapshot s, dba_hist_database_instance di
where s.dbid              = &l_dbid
    and di.dbid             = &l_dbid
    and s.instance_number   = &l_instance_number
    and di.instance_number  = &l_instance_number
    and di.dbid             = s.dbid
    and di.instance_number  = s.instance_number
    and di.startup_time     = s.startup_time
    and s.begin_interval_time > to_date(to_char(sysdate,'YYYY-MM-DD') || ' 07:40:00','YYYY-MM-DD HH24:MI:SS')
    and s.end_interval_time<to_date(to_char(sysdate,'YYYY-MM-DD') || ' 18:40:00','YYYY-MM-DD HH24:MI:SS')
  order by db_name, instance_name, snap_id;
set veri off;
set feedback off;
set head off
set linesize 1500;
set termout on;
spool $AWRLOG
select output from table(dbms_workload_repository.AWR_REPORT_HTML(&l_dbid,&l_instance_number,&l_snap_start,&l_snap_end));
spool off;
exit;
EOF
exit 0